/**
 * 
 */
package com.easidea.tim.service.inventory;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.easidea.tim.dao.inventory.StorageDao;
import com.easidea.tim.dao.inventory.StorageHistoryDao;
import com.easidea.tim.entity.commodity.Product;
import com.easidea.tim.entity.inventory.StorageHistory;

/**
 * 库存数据调度任务业务处理模块
 * @author Harrison
 */
public class StorageTransformJob {

	private StorageDao storageDao;
	private StorageHistoryDao storageHistoryDao;
	final private static Logger log = Logger.getLogger(StorageTransformJob.class);
	
	/**
	 * 定期查询历史库存，过滤掉不统计的数据
	 * 插入当前的全库存数据到历史表
	 * 删除18个月前的历史数据
	 */
	public void transformStorage2DaliyHistory(){
	
		List<StorageHistory> destList = getCurrentStorageList();
		
		storageHistoryDao.getHibernateTemplate().saveOrUpdateAll(destList);
		
		removeOldStorageHistory();
	}
	
	
	private List<StorageHistory> getCurrentStorageList(){
		final String sql = "SELECT " +
			" p.id," +
			" p.name," +
			" p.color_code," +
			" st.sample_flag," +
			" COUNT(st.id)," +
			" SUM(st.meter_num)" +
			" FROM tim_commodity_product p, tim_inventory_storage st" +
			" WHERE st.product_id = p.id AND p.deleted <> 1 AND st.deleted <> 1" +
			" AND p.dummy_flag = false" +
			" GROUP BY p.id, st.sample_flag";
		return (List<StorageHistory>)storageHistoryDao.getHibernateTemplate().execute(new HibernateCallback(){
			public List<StorageHistory> doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery query = session.createSQLQuery(sql);
				List<Object[]> result = (List<Object[]>)query.list();
				List<StorageHistory> rtnList = new ArrayList<StorageHistory>();
				if(result != null && result.size()>0){
					for(Object[] obj: result){
						StorageHistory sth = new StorageHistory();
						if(obj[0] != null){
							Product p = new Product();
							p.setId(((BigInteger)obj[0]).longValue());
							sth.setProduct(p);
						}
						if(obj[1] != null) sth.setProductName((String)obj[1]);
						if(obj[2] != null) sth.setColorCode((String)obj[2]);
						if(obj[3] != null) sth.setSampleFlag((Boolean)obj[3]);
						if(obj[4] != null) sth.setPiNum(((BigInteger)obj[4]).intValue());
						if(obj[5] != null) sth.setMeterNum(((BigDecimal)obj[5]).doubleValue());
						rtnList.add(sth);
					}
				}
				return rtnList;
			}
		});
	}
	
	/**
	 * 物理删除18个月前的库存数据
	 */
	private void removeOldStorageHistory(){
		Calendar calendar = GregorianCalendar.getInstance();
		calendar.add(Calendar.MONTH, -18);
		int calcMonth = (calendar.get(Calendar.MONTH)+1);
		calendar.set(Calendar.DAY_OF_MONTH, 1);
		
		final String sql = "delete from " + StorageHistory.class.getName() + " sh where sh.createTime < '" + calendar.get(Calendar.YEAR) + "-" + (calcMonth<10?"0"+calcMonth:calcMonth) + "-01'";
		log.debug("storage history delete sql : " + sql);

		Integer counts = (Integer)storageHistoryDao.getHibernateTemplate().bulkUpdate(sql);
		log.info("remove storage history removed counts: " + counts);
	}
	
	
	

	/**
	 * @return the storageDao
	 */
	public StorageDao getStorageDao() {
		return storageDao;
	}

	/**
	 * @param storageDao the storageDao to set
	 */
	public void setStorageDao(StorageDao storageDao) {
		this.storageDao = storageDao;
	}

	/**
	 * @return the storageHistoryDao
	 */
	public StorageHistoryDao getStorageHistoryDao() {
		return storageHistoryDao;
	}

	/**
	 * @param storageHistoryDao the storageHistoryDao to set
	 */
	public void setStorageHistoryDao(StorageHistoryDao storageHistoryDao) {
		this.storageHistoryDao = storageHistoryDao;
	}
}
